package group32.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.james.mime4j.field.datetime.DateTime;


public class Connect {
public static Connection conn;

public Connect(){
	try {
		Class.forName("com.mysql.jdbc.Driver");
		String serverName = "fling.seas.upenn.edu";
		String database = "vdurey";
		String url = "jdbc:mysql://" + serverName + "/" + database;
		String user = "vdurey";
		String password = "cis550azerty";
		
		conn = DriverManager.getConnection(url, user, password);
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
		System.exit(1);
	} catch (SQLException e) {
		e.printStackTrace();
		System.exit(1);
	}
}
public int login(String username, String password){
	int userID=-1;
	try {
		Statement s = conn.createStatement();
		s.executeQuery("Select U.password as password FROM users U where username='"+username+"'");
	ResultSet rs = s.getResultSet();
	if(rs==null)return -1;
	
		while(rs.next()){;
		String pass=rs.getString("password");
		System.out.println(pass);
		if(pass==null)return -1;
		if(password.equals(pass)){
			s.executeQuery("Select userID from users where username='"+username+"'");
			rs= s.getResultSet();
			rs.next();
			return rs.getInt("userID");
		}
	}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return userID;
	
}
public ResultSet getUpdates(int userID) {
ResultSet rs=null;
	try{
String query= "Select DISTINCT Temp.firstname, Temp.lastname, Temp.photoID as photoID, Temp.userID as userID, Temp.ts as ts,Temp.url as url ";
	query+=" from (Select DISTINCT U.firstname, U.lastname, P.photoID as photoID, up.userID as userID, P.timestamp as ts,P.url as url,";
		query+=" P.visibility as vis from users U, photos P, userphoto up";
		query+=" where U.userID=up.userID AND up.photoID = P.photoID order by P.timestamp LIMIT 800)Temp , photofriendsvisibility PFV";
		query+=" where (PFV.friendsID= 32041 OR Temp.vis=1);";
	
	System.out.println(query);			
	Statement s = conn.createStatement();
		s.executeQuery(query);
		rs= s.getResultSet();
		System.out.println("In the update");
		return rs;
	}catch(SQLException e){
	e.printStackTrace();	
	}
	return null;
}
public ResultSet getTagsUpdates(int userID) {
	ResultSet rs=null;
		try{
	String query="Select DISTINCT u.firstname, u.lastname, Temp.photoID as photoID, Temp.url, Temp.timestamp ts, Temp.taggerID, Temp.tag FROM (";
	query+="Select Distinct PT.photoID as photoID, P.url as url, P.visibility as vis,  PT.tag, PT.timestamp,";
	query+=" PT.userID taggerID from users U, phototags PT, photos P, friends f";
	query+=" where PT.userID= f.friendsID AND PT.photoID= P.photoID";
	query+=" AND f.userID= "+userID+" order by PT.timestamp DESC)Temp, photofriendsvisibility PFV , users u";
	query+=" where (PFV.friendsID= "+userID+" OR Temp.vis=1)  and Temp.photoID=PFV.photoID and u.userID= Temp.taggerID;";
	System.out.println(query);			
		Statement s = conn.createStatement();
			s.executeQuery(query);
			rs= s.getResultSet();
			System.out.println("In the update");
			return rs;
		}catch(SQLException e){
		e.printStackTrace();	
		}
		return null;
	}
public ResultSet getUserType(int userID) {
	ResultSet rs=null;
	try{
String query="Select u.usertype type FROM users u where u.userID="+userID;
System.out.println(query);			
	Statement s = conn.createStatement();
		s.executeQuery(query);
		rs= s.getResultSet();
		System.out.println("In the update");
		return rs;
	}catch(SQLException e){
	e.printStackTrace();	
	}
	return null;
}
public ResultSet searchIDs(String keys) {
	ResultSet rs=null;
	try{
String query="Select ivt.photoID, count(ivt.tag) as count from invertedtagstable ivt where ivt.tag LIKE '%"+keys+"%' group by ivt.photoID;";
System.out.println(query);			
	Statement s = conn.createStatement();
		s.executeQuery(query);
		rs= s.getResultSet();
		System.out.println("In the search");
		return rs;
	}catch(SQLException e){
	e.printStackTrace();	
	}
	return null;
	}
public ResultSet searchInfo(int id, int userID) {
	ResultSet rs=null;
	try{
String query="Select P.description, P.url, P.visibility, UP.userID as photoOwner from photos P, photofriendsvisibility PFV , userphoto UP where (PFV.friendsID= "+userID+" OR P.visibility=1) AND P.photoid="+id+" and UP.photoID= P.photoID;";
System.out.println(query);			
	Statement s = conn.createStatement();
		s.executeQuery(query);
		rs= s.getResultSet();
		System.out.println("In the search info");
		return rs;
	}catch(SQLException e){
	e.printStackTrace();	
	}
	return null;
	}
}